package id.ac.amikom.libs;

import java.text.SimpleDateFormat;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class AdemDBHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "db_adem";

	public AdemDBHelper(Context context) {
		super(context, DATABASE_NAME, null, 37);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE if not exists info "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "public_id INTEGER, " + "title TEXT, " + "start DATE, "
				+ "end DATE, " + "detail TEXT, " + "status INTEGER);");

		db.execSQL("CREATE TABLE if not exists news "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "public_id INTEGER, " + "title TEXT, " + "tgl DATE, "
				+ "permalink TEXT, " + "detail TEXT, " + "status INTEGER);");

		// status use for the schedule is set to weekly (status 1 - weekly)
		db.execSQL("CREATE TABLE if not exists calendar "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title TEXT, "
				+ "start TIMESTAMP NOT NULL DEFAULT current_timestamp, "
				+ "end TIMESTAMP NOT NULL DEFAULT current_timestamp, "
				+ "location TEXT, " + "detail TEXT, " + "status INTEGER);");

		db.execSQL("CREATE TABLE if not exists login "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "usr TEXT, "
				+ "is_mhs INTEGER, " + "name TEXT, " + "logdate DATE,"
				+ "calendar INTEGER);");

		db.execSQL("CREATE TABLE if not exists materi "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "dosen TEXT, "
				+ "mkl TEXT, " + "judul TEXT, " + "file TEXT, "
				+ "upload TIMESTAMP NOT NULL DEFAULT current_timestamp, "
				+ "sinopsis TEXT, size TEXT);");

		db.execSQL("CREATE TABLE if not exists shout "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, public_id INTEGER, "
				+ "nid TEXT, " + "name TEXT, alias TEXT, " + "msg TEXT, "
				+ "foto TEXT, " + "sts TEXT, "
				+ "time TIMESTAMP NOT NULL DEFAULT current_timestamp, "
				+ "via TEXT);");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS shout");
		//db.execSQL("DROP TABLE IF EXISTS login");
		// db.execSQL("DROP TABLE IF EXISTS materi");
		// db.execSQL("DROP TABLE IF EXISTS calendar");

		onCreate(db);

	}

	public void insertLogin(String usr, int is_mhs, String name) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss");
		Date date = new Date();

		values.put("usr", usr);
		values.put("is_mhs", is_mhs);
		values.put("name", name);		
		values.put("logdate", dateFormat.format(date));
		values.put("calendar", 0);

		Cursor log = getLogin();
		if (log.getCount() > 0)
			deleteLogin();

		db.insert("login", null, values);

		db.close();
	}

	public Cursor getLogin() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select _id,usr,is_mhs,name,logdate,calendar "
				+ "From login", new String[] {});

		return cur;
	}

	public boolean isMhs() {
		Cursor log = getLogin();
		String sts = "0";
		if (log.moveToFirst()) {
			do {
				sts = log.getString(log.getColumnIndex("is_mhs"));
			} while (log.moveToNext());
		}

		if (sts.equals("1"))
			return true;
		else
			return false;
	}

	public int getCalendarId() {
		Cursor log = getLogin();
		int id = 0;
		if (log.moveToFirst()) {
			do {
				id = log.getInt(log.getColumnIndex("calendar"));
			} while (log.moveToNext());
		}

		return id;
	}

	public void updateCalendarId(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("calendar", id);

		db.update("login", values, null, null);
		db.close();
	}
	
	public void updateAlias(String usr, String alias){
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Update shout set alias='"+ alias +"' Where nid='"+ usr +"'";

		db.execSQL(str);
	}

	public void insertInfo(String public_id, String title, String start,
			String end, String detail) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("public_id", public_id);
		values.put("title", title.trim());
		values.put("detail", detail);
		values.put("start", start);
		values.put("end", end);
		values.put("status", 0);

		db.insert("info", null, values);
		db.close();
	}

	public Cursor getInfo() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select _id,public_id,title,start,end,status "
				+ "From info Order By public_id Desc", new String[] {});

		return cur;
	}

	public Cursor getDetailInfo(String public_id) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,public_id,title,start,end,status,detail "
						+ "From info Where public_id ='" + public_id + "'",
				new String[] {});

		return cur;
	}

	public void insertNews(String public_id, String title, String tgl,
			String link, String detail) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("public_id", public_id);
		values.put("title", title.trim());
		values.put("detail", detail);
		values.put("tgl", tgl);
		values.put("permalink", link);
		values.put("status", 0);

		db.insert("news", null, values);
		db.close();
	}

	public Cursor getNews() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,public_id,title,tgl,permalink,status "
						+ "From news Order By public_id Desc", new String[] {});
		return cur;
	}

	public Cursor getDetailNews(String public_id) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,public_id,title,tgl,permalink,status,detail "
						+ "From News Where public_id ='" + public_id + "'",
				new String[] {});

		return cur;
	}

	public void insertCalendar(String title, String start, String end,
			String location, String detail, int is_weekly) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("title", title.trim());
		values.put("start", start);
		values.put("end", end);
		values.put("location", location);
		values.put("detail", detail);
		values.put("status", is_weekly);

		db.insert("calendar", null, values);
		db.close();
	}

	public Cursor getCalendar() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,title,start,end,location,detail,status "
						+ "From calendar Order By _id", new String[] {});

		return cur;

	}

	public void insertMateri(String dosen, String mkl, String judul,
			String file, String upload, String sinopsis, String size) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("dosen", dosen.trim());
		values.put("mkl", mkl.trim());
		values.put("judul", judul.trim());
		values.put("file", file.trim());
		values.put("upload", upload);
		values.put("sinopsis", sinopsis);
		values.put("size", size);

		db.insert("materi", null, values);
		db.close();

	}

	public Cursor getMateri() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,dosen,mkl,judul,file,upload,sinopsis,size "
						+ "From materi Order By upload Desc", new String[] {});

		return cur;
	}

	public Cursor getDetailMateri(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,dosen,mkl,judul,file,upload,sinopsis,size "
						+ "From materi Where _id =" + id, new String[] {});

		return cur;
	}

	public int getLastNewsId() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor pub_id = db
				.rawQuery(
						"Select _id,public_id From news Order By public_id Desc Limit 1",
						new String[] {});

		int lastid = 0;
		if (pub_id.moveToFirst()) {
			do {
				lastid = Integer.parseInt(pub_id.getString(1).toString());
			} while (pub_id.moveToNext());
		}

		pub_id.close();

		return lastid;
	}

	public int getLastInfoId() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor pub_id = db
				.rawQuery(
						"Select _id,public_id From info Order By public_id Desc Limit 1",
						new String[] {});

		int lastid = 0;
		if (pub_id.moveToFirst()) {
			do {
				lastid = Integer.parseInt(pub_id.getString(1).toString());
			} while (pub_id.moveToNext());
		}

		pub_id.close();

		return lastid;
	}

	public String getLastMateri() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor pub_id = db.rawQuery(
				"Select _id,upload From materi Order By _id Limit 1",
				new String[] {});

		String lastid = "2012-01-01 00:00:00";
		if (pub_id.moveToFirst()) {
			do {
				lastid = pub_id.getString(1).toString();
			} while (pub_id.moveToNext());
		}

		pub_id.close();

		return lastid.trim();
	}

	public void insertShout(String pid, String nid, String name, String alias, String msg,
			String foto, String sts, String time, String via) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("public_id", pid);
		values.put("nid", nid.trim());
		values.put("name", name.trim());
		values.put("alias", alias.trim());
		values.put("msg", msg.trim());
		values.put("foto", foto.trim());
		values.put("sts", sts.trim());
		values.put("time", time);
		values.put("via", via.trim());

		db.insert("shout", null, values);
		db.close();

	}

	public Cursor getShout() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select _id,nid,name,alias,msg,foto,sts,time,via "
				+ "From shout Order By time Desc Limit 50", new String[] {});

		return cur;
	}

	public Cursor getUserShout() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db
				.rawQuery(
						"Select distinct(foto), nid  From shout Where foto <> '' Group By nid",
						new String[] {});

		return cur;
	}

	public int getLastShoutId() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor pub_id = db.rawQuery(
				"Select _id,public_id From shout Order By time Desc Limit 1",
				new String[] {});

		int lastid = 0;
		if (pub_id.moveToFirst()) {
			do {
				lastid = Integer.parseInt(pub_id.getString(1).toString());
			} while (pub_id.moveToNext());
		}

		pub_id.close();

		return lastid;
	}

	public void deleteInfo() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From info";

		db.execSQL(str);
	}

	public void deleteNews() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From news";

		db.execSQL(str);
	}

	public void deleteCalendar() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From calendar Where location like 'STMIK Amikom%'";

		db.execSQL(str);
	}

	public void deleteAkademikCalendar() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From calendar Where location like 'Amikom%'";

		db.execSQL(str);
	}

	public void deleteMateri() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From materi";

		db.execSQL(str);
	}

	public void deleteLogin() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From login";

		db.execSQL(str);
	}

	public void deleteShout() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From shout";

		db.execSQL(str);
	}

}
